const sqlhelper = require('../sqlhelper/sqlhelper');

module.exports = {
  queryPost(currentPage, pageSize, category_id, status, callback) {
    let connection = sqlhelper.getConnection();
    let sql = `select p.id,p.title,p.created,p.\`status\`,c.name,u.nickname FROM posts p
    left JOIN categories c on c.id = p.category_id
    left JOIN users u on u.id = p.user_id
    WHERE 1 = 1 `;
    if (category_id != 'all') {
      sql += ` and p.category_id = ${category_id} `
    }
    if (status != 'all') {
      sql += ` and p.\`status\` = '${status}' `
    }
    let offset = (currentPage - 1) * pageSize;
    sql += `LIMIT ${offset},${pageSize}`;

    connection.query(sql, (err, result) => {
      callback(err, result);
    })
  },
  getCountByQuery(category_id, status, callback) {
    let connection = sqlhelper.getConnection();
    let sql = `SELECT count(id) as total  FROM posts p 
    WHERE 1 = 1 `;
    if (category_id != 'all') {
      sql += ` and p.category_id = ${category_id} `
    }
    if (status != 'all') {
      sql += ` and p.\`status\` = '${status}' `
    }
    connection.query(sql, (err, result) => {
      callback(err, result);
    })
  }
}